﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using WXY.Course.Model;

namespace WXY.Course.DAL
{
    public class AccountDAL
    {
        private static SqlHelper sqliteHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CourseDB"].ConnectionString);

        public static UserInfo Get(string userName, string password)
        {
            string sql = "select * from TUser where Name=@Name and Password=@Password";

            SqlParameter[] parameter = {
                new SqlParameter("@Name", userName),
                new SqlParameter("@Password", password)
            };

            return sqliteHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                UserInfo userInfo = null;
                while (reader.Read())
                {
                    userInfo = new UserInfo();
                    userInfo.Code = Convert.ToInt32(reader["Code"]);
                    userInfo.Name = reader["Name"].ToString();
                    userInfo.Password = reader["Password"].ToString();
                    userInfo.RealName = reader["RealName"].ToString();
                    userInfo.State = Convert.ToInt32(reader["State"]);
                    userInfo.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    int createUserId = Convert.ToInt32(reader["CreateUserId"]);
                    userInfo.CreateUser = createUserId == 0 ? null : Get(createUserId, true);
                }
                return userInfo;
            }, parameter);
        }

        public static UserInfo GetByName(string userName)
        {
            string sql = "select * from TUser where Name=@Name";

            SqlParameter[] parameter = {
                new SqlParameter("@Name", userName)
            };

            return sqliteHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                UserInfo userInfo = null;
                while (reader.Read())
                {
                    userInfo = new UserInfo();
                    userInfo.Code = Convert.ToInt32(reader["Code"]);
                    userInfo.Name = reader["Name"].ToString();
                    userInfo.Password = reader["Password"].ToString();
                    userInfo.RealName = reader["RealName"].ToString();
                    userInfo.State = Convert.ToInt32(reader["State"]);
                    userInfo.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    int createUserId = Convert.ToInt32(reader["CreateUserId"]);
                    userInfo.CreateUser = createUserId == 0 ? null : Get(createUserId, true);
                }
                return userInfo;
            }, parameter);
        }

        public static int UpdatePass(int id, string newPass)
        {
            string stmtId = "update TUser set Password=@Password where Code=@Code";

            SqlParameter[] parameters = {
                new SqlParameter("@Code", id),
                new SqlParameter("@Password", newPass)
            };

            return sqliteHelper.ExecuteNonQuery(stmtId, CommandType.Text, parameters);
        }

        public static IList<UserInfo> GetList(PageParams pageParams)
        {
            string sql = @"select top (@PageSize) * from (
                            select row_number() over(order by code) as rownumber,* from TUser
                           ) 
                           A where rownumber > (@PageIndex -1 ) * @PageSize";

            SqlParameter[] parameter = {
                new SqlParameter("@PageIndex", pageParams.PageIndex),
                new SqlParameter("@PageSize", pageParams.PageSize)
            };

            pageParams.RecordCount = GetCount();

            return sqliteHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                IList<UserInfo> userList = new List<UserInfo>();
                while (reader.Read())
                {
                    UserInfo userInfo = new UserInfo();
                    userInfo.Code = Convert.ToInt32(reader["Code"]);
                    userInfo.Name = reader["Name"].ToString();
                    userInfo.Password = reader["Password"].ToString();
                    userInfo.RealName = reader["RealName"].ToString();
                    userInfo.State = Convert.ToInt32(reader["State"]);
                    userInfo.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    int createUserId = Convert.ToInt32(reader["CreateUserId"]);
                    userInfo.CreateUser = createUserId == 0 ? null : Get(createUserId, true);
                    userList.Add(userInfo);
                }
                return userList;
            }, parameter);
        }

        private static int GetCount()
        {
            string sql = @"select count(*) from TUser";

            object o = sqliteHelper.ExecuteScalar(sql, CommandType.Text, null);

            return Convert.ToInt32(o);
        }

        public static int Insert(UserInfo info)
        {
            string sql = @"insert into TUser(Name,Password,RealName,CreateTime,State,CreateUserId)
                           values(@Name,@Password,@RealName,@CreateTime,@State,@CreateUserId);
                           select @@identity";
            SqlParameter[] parameter = {
                new SqlParameter("@Name", info.Name),
                new SqlParameter("@Password", info.Password),
                new SqlParameter("@RealName", info.RealName),
                new SqlParameter("@CreateTime", DateTime.Now),
                new SqlParameter("@State", info.State),
                new SqlParameter("@CreateUserId", info.CreateUser.Code)
            };

            object o = sqliteHelper.ExecuteScalar(sql, CommandType.Text, parameter);

            return Convert.ToInt32(o);
        }

        public static int Update(UserInfo info)
        {
            string sql = @"update TUser set Password=@Password
                          where Code=@Code";

            SqlParameter[] parameter = {
                new SqlParameter("@Code",info.Code),
                new SqlParameter("@Password", info.Password)
            };

            return sqliteHelper.ExecuteNonQuery(sql, CommandType.Text, parameter);
        }
        public static int Delete(int code)
        {
            string sql = "delete from TUser where Code=@Code";
            SqlParameter[] parameter = {
                new SqlParameter("@Code",code)
            };

            return sqliteHelper.ExecuteNonQuery(sql, CommandType.Text, parameter);
        }

        public static UserInfo Get(int code, bool isFather = false)
        {
            string sql = @"select * from TUser where Code=@Code";

            SqlParameter[] parameter = {
                new SqlParameter("@Code", code)
            };

            return sqliteHelper.ExecuteQuery(sql, CommandType.Text, (reader) =>
            {
                UserInfo userInfo = new UserInfo();
                while (reader.Read())
                {
                    userInfo.Code = Convert.ToInt32(reader["Code"]);
                    userInfo.Name = reader["Name"].ToString();
                    userInfo.Password = reader["Password"].ToString();
                    userInfo.RealName = reader["RealName"].ToString();
                    userInfo.State = Convert.ToInt32(reader["State"]);
                    userInfo.CreateTime = Convert.ToDateTime(reader["CreateTime"]);
                    int createUserId = Convert.ToInt32(reader["CreateUserId"]);
                    userInfo.CreateUser = (createUserId == 0 || isFather) ? null : Get(createUserId, true);
                }
                return userInfo;
            }, parameter);
        }
    }
}
